/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package databaseLayer;

import java.sql.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import javax.naming.NamingException;

import statistics.*;

/**
 *
 * @author Rigsby Chi
 */
public class StatisticsLookUp {

    public static void main(String arg[]){
        HouseStatistics houseStatistics[] = StatisticsLookUp.getHouseStatistics();
        for (int i = 0; i < houseStatistics.length; i++) {
            System.out.println("Gross: " + houseStatistics[i].getGross());
            System.out.println("Bought: " + houseStatistics[i].getBought());
            System.out.println("Available: " + houseStatistics[i].getAvailable());
            System.out.println("Unavailable: " + houseStatistics[i].getUnavailable());
        }

        MovieSlotStatistics movieSlotStatistics = StatisticsLookUp.getMovieSlotStatistics("mv3001");
        System.out.println("Gross: " + movieSlotStatistics.getGross());
        System.out.println("Bought: " + movieSlotStatistics.getBought());
        System.out.println("Available: " + movieSlotStatistics.getAvailable());
        System.out.println("Unavailable: " + movieSlotStatistics.getUnavailable());

        MovieStatistics movieStatistics[] = StatisticsLookUp.getMovieStatistics();
        for (int i = 0; movieStatistics != null && i < movieStatistics.length; i++) {
            System.out.println("Gross: " + movieStatistics[i].getGross());
            System.out.println("Bought: " + movieStatistics[i].getBought());
            System.out.println("Available: " + movieStatistics[i].getAvailable());
            System.out.println("Unavailable: " + movieStatistics[i].getUnavailable());
        }
    }

    //all house
    public static HouseStatistics[] getHouseStatistics() {
        HouseStatistics[] houseStatistics = null;

        try {

            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();

            int numberOfHouse = 0;
            int seat[] = null;

            PreparedStatement pstmt = con.prepareStatement("SELECT seat FROM House order by hid", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = pstmt.executeQuery();

            if (rs != null && rs.next()) {
                rs.last();
                numberOfHouse = rs.getRow();
                rs.beforeFirst();
                seat = new int[numberOfHouse];
                for (int i = 0; rs.next(); i++) {
                    seat[i] = rs.getInt("seat");
                }

                int gross[] = new int[4];
                int bought[] = new int[4];
                int available[] = new int[4];
                int unavailable[] = new int[4];

                pstmt = con.prepareStatement("SELECT count(hid), hid FROM movieslot WHERE slotstate=1 group by hid");
                rs = pstmt.executeQuery();
                while (rs.next()) {
                    available[rs.getInt(2) - 1] = seat[rs.getInt(2) - 1] * rs.getInt(1);
                }

                pstmt = con.prepareStatement("SELECT seatno, hid, price, moneyused FROM movieslot, buyrecord where (RECORDSTATE=0 or RECORDSTATE=1) and slotstate=1 and movieslot.slotid=buyrecord.slotid");
                rs = pstmt.executeQuery();

                while (rs.next()) {
                    int houseID = rs.getInt("hid");
                    int amount = rs.getString("seatno").length() / 3;
                    available[houseID - 1] -= amount;
                    gross[houseID - 1] = amount * rs.getInt("price");
                    bought[houseID - 1] += amount;
                }
                
                for(int i = 0; i < numberOfHouse; i++){
                    pstmt = con.prepareStatement("select count(*) from SeatDisable, Movieslot where SeatDisable.slotID=Movieslot.slotID and HID=?");
                    pstmt.setInt(1, i+1);
                    rs = pstmt.executeQuery();
                    if (rs != null && rs.next()) {
                        unavailable[i] = rs.getInt(1);
                        available[i] -= unavailable[i];
                    }
                }
                
                houseStatistics = new HouseStatistics[numberOfHouse];

                for (int i = 0; i < numberOfHouse; i++) {
                    houseStatistics[i] = new HouseStatistics();
                    houseStatistics[i].setGross(gross[i]);
                    houseStatistics[i].setBought(bought[i]);
                    houseStatistics[i].setAvailable(available[i]);
                    houseStatistics[i].setUnavailable(unavailable[i]);
                }
            }

            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException s) {
            s.printStackTrace();
            houseStatistics = null;
        } catch (NamingException e) {
            e.printStackTrace();
            houseStatistics = null;
        }
        return houseStatistics;
    }

    //a slot
    public static MovieSlotStatistics getMovieSlotStatistics(String movieSlotID) {
        MovieSlotStatistics movieSlotStatistics = new MovieSlotStatistics();

        try {
            
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();

            
            int available = 0;
            int price = 0;
            int gross = 0;
            int bought = 0;
            int unavailable = 0;
            
            PreparedStatement pstmt = con.prepareStatement("SELECT seat from movieslot,house where slotID=? and house.hid=movieslot.hid");
            pstmt.setString(1, movieSlotID);
            ResultSet rs = pstmt.executeQuery();
            if (rs != null && rs.next()) {
                available = rs.getInt(1);
            }
            
            System.out.println(available);
            
            pstmt = con.prepareStatement("SELECT seat, price, seatno, moneyused FROM house, movieslot, buyrecord WHERE  (RECORDSTATE=0 or RECORDSTATE=1) and slotstate=1 and buyrecord.slotID=? and movieslot.slotid=buyrecord.slotID and movieslot.hid=house.hid", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            pstmt.setString(1, movieSlotID);
            rs = pstmt.executeQuery();
            if (rs != null && rs.next()) {
                price = rs.getInt("price");
                rs.beforeFirst();
                while (rs.next()) {
                    int amount = rs.getString("seatno").length() / 3;
                    available -= amount;
                    gross += amount * price;
                    bought += amount;
                }
                
                pstmt = con.prepareStatement("select count(*) from seatdisable where SLOTID=?");
                pstmt.setString(1, movieSlotID);
                rs = pstmt.executeQuery();
                if (rs != null && rs.next()) {
                    unavailable = rs.getInt(1);
                    available -= unavailable;
                }
            }
            
            movieSlotStatistics.setGross(gross);
            movieSlotStatistics.setBought(bought);
            movieSlotStatistics.setAvailable(available);
            movieSlotStatistics.setUnavailable(unavailable);
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException s) {
            s.printStackTrace();
            movieSlotStatistics = new MovieSlotStatistics();
        } catch (NamingException c) {
            c.printStackTrace();
            movieSlotStatistics = new MovieSlotStatistics();
        }
        return movieSlotStatistics;
    }

    //add movie
    public static MovieStatistics[] getMovieStatistics() {
        MovieStatistics[] movieStatistics = null;

        try {

            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();
            
            int numberOfMovie = 0;

            PreparedStatement pstmt = con.prepareStatement("SELECT name FROM Movie order by name", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = pstmt.executeQuery();
            if (rs != null && rs.next()) {
                rs.last();
                numberOfMovie = rs.getRow();
                rs.beforeFirst();

                String movieName[] = new String[numberOfMovie];
                for (int i = 0; rs.next(); i++) {
                    movieName[i] = rs.getString("Name");
                }

                movieStatistics = new MovieStatistics[numberOfMovie];

                int gross[] = new int[numberOfMovie];
                int bought[] = new int[numberOfMovie];
                int available[] = new int[numberOfMovie];
                int unavailable[] = new int[numberOfMovie];

                pstmt = con.prepareStatement("SELECT seat, moviename from movieslot, house where slotstate=1 and movieslot.hid=house.hid");
                rs = pstmt.executeQuery();

                while (rs.next()) {
                    for (int i = 0; i < numberOfMovie; i++) {
                        if (movieName[i].equals(rs.getString("moviename"))) {
                            available[i] += rs.getInt("seat");
                        }
                    }
                }

                pstmt = con.prepareStatement("SELECT buyrecord.moviename, seatno, price, moneyused from buyrecord,movieslot where  (RECORDSTATE=0 or RECORDSTATE=1) and slotstate=1 and buyrecord.slotid=movieslot.slotid");
                rs = pstmt.executeQuery();

                while (rs.next()) {
                    for (int i = 0; i < numberOfMovie; i++) {
                        int amount = rs.getString("seatno").length() / 3;
                        int price = rs.getInt("price");
                        if (movieName[i].equals(rs.getString("moviename"))) {
                            available[i] -= amount;
                            gross[i] += amount * price;
                            bought[i] += amount;
                        }
                    }
                }
                
                for(int i = 0; i < numberOfMovie; i++){
                    pstmt = con.prepareStatement("select count(*) from SeatDisable where SLOTID like ?");
                    pstmt.setString(1, movieName[i] + "-%");
                    rs = pstmt.executeQuery();
                    if (rs != null && rs.next()) {
                        unavailable[i] = rs.getInt(1);
                        available[i] -= unavailable[i];
                    }
                }

                for (int i = 0; i < numberOfMovie; i++) {
                    movieStatistics[i] = new MovieStatistics();
                    movieStatistics[i].setGross(gross[i]);
                    movieStatistics[i].setBought(bought[i]);
                    movieStatistics[i].setAvailable(available[i]);
                    movieStatistics[i].setUnavailable(unavailable[i]);
                }
            }
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException s) {
            s.printStackTrace();
            movieStatistics = null;
        } catch (NamingException c) {
            c.printStackTrace();
            movieStatistics = null;
        }
        return movieStatistics;
    }
}
